sql server

您所在的位置：网站首页 › sql server查询表的所有列名 › sql server

sql server

2024-02-14 01:34| 来源: 网络整理| 查看: 265

方式一：显示多种信息

sp_help table_name

方式二：只显示字段

sp_columns table_name

方式三：自定义 SELECT SO.name 表名, SC.name 表列名, SC.colid 索引, ST.name 类型 FROM sysobjects SO, -- 对象表 syscolumns SC, -- 列名表 systypes ST -- 数据类型表 WHERE SO.id = SC.id AND SO.xtype = 'U' -- 类型U表示表，V表示视图 AND SO.status >= 0 -- status >= 0 为非系统对象 AND SC.xtype = ST.xusertype AND SO.name = 'table_name' -- 某张特定表 ORDER BY SO.name, SC.colorder -- 按表名、列名排序

　　如果要查看视图信息，只需要将SO.xtype = 'U'该为SO.xtype = 'V' 即可。

方式四：自定义

SELECT a.name AS 字段名 , CASE WHEN ( SELECT COUNT(*) FROM sysobjects WHERE name IN ( SELECT name FROM sysindexes WHERE id = a.id AND indid IN ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid IN ( SELECT colid FROM syscolumns WHERE id = a.id AND name = a.name ) ) ) AND xtype = 'PK' ) > 0 THEN '√' ELSE '' END AS 主键, b.name AS 类型 , CASE WHEN a.isnullable = 0 THEN '√' ELSE '' END AS 必填 , isnull(g.[value], '') AS 字段描述 FROM syscolumns a LEFT JOIN systypes b ON a.xtype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name 'dtproperties' LEFT JOIN syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid= g.minor_id where d.name='MP_GuaranteeMoneyApply'--所要查询的表 order by a.id,a.colorder

--方式4：

SELECT a.name AS 字段名, b.name AS 类型, a.length, CASE WHEN a.isnullable = 0 THEN '√' ELSE '' END AS 必填 , CASE WHEN ( SELECT COUNT(*) FROM sysobjects WHERE name IN ( SELECT name FROM sysindexes WHERE id = a.id AND indid IN ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid IN ( SELECT colid FROM syscolumns WHERE id = a.id AND name = a.name ) ) ) AND xtype = 'PK' ) > 0 THEN '√' ELSE '' END AS 主键 , isnull(g.[value], '') AS 字段描述 FROM syscolumns a LEFT JOIN systypes b ON a.xtype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name 'dtproperties' LEFT JOIN syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid= g.minor_id where d.name='MP_SP_TP_ApproveAgeing'--所要查询的表 order by a.id,a.colorder

【本文地址】

sql server

sql server

今日新闻

推荐新闻